﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

namespace HRdex_Final.Forms
{
    public partial class WebForm7 : System.Web.UI.Page
    {
        string SessionID()
        {
            return Session["EmployeeID"].ToString();
        }
        SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\HRdexDB.mdf;Integrated Security=True;User Instance=True");
        protected void Page_Load(object sender, EventArgs e)
        {
            string SubstitutionID = Request.QueryString["SubstitutionID"];
           
            //Selected Value Employee Information
            SqlCommand cmd2 = new SqlCommand("SELECT EmployeeID, LastName + ', ' + FirstName + ' ' + MiddleName AS Name," +
                "DeptID, CollegeID FROM formEmployees WHERE (EmployeeID=" + SessionID() + ")", conn);
            SqlDataAdapter da2 = new SqlDataAdapter(cmd2);
            DataTable dt2 = new DataTable("dt2");

            da2.Fill(dt2);
            
            txtName.Text = dt2.Rows[0].ItemArray[1].ToString();
            ddlDept.SelectedValue = dt2.Rows[0].ItemArray[2].ToString();
            ddlCollege.SelectedValue = dt2.Rows[0].ItemArray[3].ToString();
            lblConfirmationMessage.Visible = false;
            txtDays.Visible = false;
            txtFromSub.Visible = false;
            txtToSub.Visible = false;
            txtUnitLab.Visible = false;
            txtUnitLec.Visible = false;
            txtTime.Visible = false;
            txtSubID.Visible = false;
            txtName.Visible = false;
            ddlCollege.Enabled = false;
            ddlDept.Enabled = false;

            Select();
        }

        protected void btnSave_Click(object sender, EventArgs e)
        {
            string SubstitutionID = Request.QueryString["SubstitutionID"];

            if (txtSubID.Text != SubstitutionID)
            {



                SqlCommand cmd = new SqlCommand("Insert INTO formSubstitution(EmpID, Rank,DeptID, CollegeID,presentLoadAssign, " +
                    "PayingHour, FacultySubID, FromDateSubstituted,ToDateSubstituted, nOfDays, Code,Section,Time," +
                    "Days, Room, Subject,UnitLec, UnitLab) " +
                    "VALUES (@empID, @rank, @deptID,@collegeID, @PresentLoad, @payinghour, @facultySubID, @fDateSub, @toDateSub," +
                    "@nDays, @code, @section, @time, @days, @Room, @subject, @unitLec, @unitLab)", conn);
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                conn.Open();
                cmd.Parameters.Add("@empID", SqlDbType.Int).Value = SessionID();
                cmd.Parameters.Add("@rank", SqlDbType.VarChar).Value = txtRank.Text;
                cmd.Parameters.Add("@deptID", SqlDbType.Int).Value = ddlDept.SelectedValue;
                cmd.Parameters.Add("@collegeID", SqlDbType.Int).Value = ddlCollege.SelectedValue;
                cmd.Parameters.Add("@PresentLoad", SqlDbType.VarChar).Value = txtPresentLoad.Text;
                cmd.Parameters.Add("@payingHour", SqlDbType.VarChar).Value = txtPayingHour.Text;
                cmd.Parameters.Add("@facultySubID", SqlDbType.Int).Value = ddlFac.SelectedValue;
                cmd.Parameters.Add("@fDateSub", SqlDbType.VarChar).Value = txtFromSub.Text;
                cmd.Parameters.Add("@toDateSub", SqlDbType.VarChar).Value = txtToSub.Text;
                cmd.Parameters.Add("@nDays", SqlDbType.Int).Value = txtTotalDays.Text;
                cmd.Parameters.Add("@Code", SqlDbType.VarChar).Value = txtcode.Text;
                cmd.Parameters.Add("@Section", SqlDbType.VarChar).Value = txtSection.Text;
                cmd.Parameters.Add("@Time", SqlDbType.VarChar).Value = ddlsThour.Text + ":" + ddlstMin.Text;
                cmd.Parameters.Add("@Days", SqlDbType.VarChar).Value = ddlDays.Text;
                cmd.Parameters.Add("@Room", SqlDbType.VarChar).Value = txtRoom.Text;
                cmd.Parameters.Add("@Subject", SqlDbType.VarChar).Value = txtSubject.Text;
                cmd.Parameters.Add("@unitLec", SqlDbType.Int).Value = ddlUnitLec.Text;
                cmd.Parameters.Add("@unitLab", SqlDbType.Int).Value = ddlLab.Text;
                cmd.ExecuteNonQuery();
                conn.Close();
                lblConfirmationMessage.Visible = true;
                lblConfirmationMessage.Text = "**You successfully add substitution request. Please wait for the approval of your Department, College and HR Department. Thank you for using HRdex! :D";

            }
            else
            {
                Edit();
                notification();
                lblConfirmationMessage.Visible = true;
                lblConfirmationMessage.Text = "**A remark has been applied!";
            }
        }

        public void Select()
        {
            string SubstitutionID = Request.QueryString["SubstitutionID"];


            if (SubstitutionID != null)
            {
                ddlDays.Visible = false;
                ddlLab.Visible = false;
                ddlsThour.Visible = false;
                ddlstMin.Visible = false;
                ddlUnitLec.Visible = false;


                //Selected Value Faculty to be Substituted
                SqlCommand cmd3 = new SqlCommand("SELECT  formSubstitution.SubstitutionID, " +
                    "formEmployees.LastName + ', ' + formEmployees.FirstName + ' ' + formEmployees.MiddleName AS Name," +
                    " formSubstitution.Rank, formSubstitution.DeptID, formSubstitution.CollegeID, formSubstitution.presentLoadAssign, " +
                    "formSubstitution.PayingHour, formSubstitution.FacultySubID, formSubstitution.FromDateSubstituted, " +
                    "formSubstitution.ToDateSubstituted, formSubstitution.nOfDays, formSubstitution.Code, formSubstitution.Section,  " +
                    "formSubstitution.Time, formSubstitution.Days, formSubstitution.Room, formSubstitution.Subject, formSubstitution.UnitLec, " +
                    "formSubstitution.UnitLab, formSubstitution.ApprovalHead, formSubstitution.ApprovalDean, formSubstitution.ApprovalHR FROM   formSubstitution INNER JOIN  formEmployees ON formSubstitution.EmpID = formEmployees.EmployeeID" +
                    " WHERE (formSubstitution.SubstitutionID = " + SubstitutionID + ")", conn);
                SqlDataAdapter da3 = new SqlDataAdapter(cmd3);
                DataTable dt3 = new DataTable("dt3");
                da3.Fill(dt3);
                txtName.Text = dt3.Rows[0].ItemArray[1].ToString();
                txtRank.Text = dt3.Rows[0].ItemArray[2].ToString();
                ddlDept.SelectedValue = dt3.Rows[0].ItemArray[3].ToString();
                ddlCollege.SelectedValue = dt3.Rows[0].ItemArray[4].ToString();
                txtPresentLoad.Text = dt3.Rows[0].ItemArray[5].ToString();
                txtPayingHour.Text = dt3.Rows[0].ItemArray[6].ToString();
                ddlFac.SelectedValue = dt3.Rows[0].ItemArray[7].ToString();
                txtFromSub.Text = dt3.Rows[0].ItemArray[8].ToString();
                txtToSub.Text = dt3.Rows[0].ItemArray[9].ToString();
                txtTotalDays.Text = dt3.Rows[0].ItemArray[10].ToString();
                txtcode.Text = dt3.Rows[0].ItemArray[11].ToString();
                txtSection.Text = dt3.Rows[0].ItemArray[12].ToString();
                txtTime.Text = dt3.Rows[0].ItemArray[13].ToString();
                txtDays.Text = dt3.Rows[0].ItemArray[14].ToString();
                txtRoom.Text = dt3.Rows[0].ItemArray[15].ToString();
                txtSubject.Text = dt3.Rows[0].ItemArray[16].ToString();
                txtUnitLec.Text = dt3.Rows[0].ItemArray[17].ToString();
                txtUnitLab.Text = dt3.Rows[0].ItemArray[18].ToString();

                SqlCommand cmdSelectEmp = new SqlCommand("SELECT formEmployees.EmployeeID, catUserLevel.catUserLevelName AS [User Level], catPositions.catPositionsName AS Position, catDepartment.DepartmentName AS Department, catCollege.CollegeName AS Office FROM formEmployees INNER JOIN catDepartment ON formEmployees.DeptID = catDepartment.DeptID INNER JOIN catCollege ON formEmployees.CollegeID = catCollege.CollegeID INNER JOIN catPositions ON formEmployees.catPositionsID = catPositions.catPositionsID INNER JOIN catUserLevel ON formEmployees.UserLevelID = catUserLevel.UserLevelID WHERE (EmployeeID = '" + SessionID() + "')", conn);
                SqlDataAdapter daSelectEmp = new SqlDataAdapter(cmdSelectEmp);
                DataTable dtSelectEmp = new DataTable("dtSelectEmp");
                daSelectEmp.Fill(dtSelectEmp);

                SqlCommand cmd31 = new SqlCommand("SELECT formEmployees.EmployeeID, catUserLevel.catUserLevelName AS [User Level], catPositions.catPositionsName AS Position, catDepartment.DepartmentName AS Department, catCollege.CollegeName AS Office FROM formEmployees INNER JOIN catDepartment ON formEmployees.DeptID = catDepartment.DeptID INNER JOIN catCollege ON formEmployees.CollegeID = catCollege.CollegeID INNER JOIN catPositions ON formEmployees.catPositionsID = catPositions.catPositionsID INNER JOIN catUserLevel ON formEmployees.UserLevelID = catUserLevel.UserLevelID WHERE (EmployeeID = '" + SessionID() + "') AND (catDepartment.DepartmentName = '" + dtSelectEmp.Rows[0].ItemArray[3].ToString() + "') OR (catCollege.CollegeName = '" + dtSelectEmp.Rows[0].ItemArray[4].ToString() + "')", conn);
                SqlDataAdapter da31 = new SqlDataAdapter(cmd31);
                DataTable dt31 = new DataTable("dt31");
                da31.Fill(dt31);
                //notYetTested

                //HR Directors Office
                if (dt31.Rows[0].ItemArray[4].ToString() == "Adamson Office" && dt31.Rows[0].ItemArray[3].ToString() == "HR Directors Office" && dt31.Rows[0].ItemArray[1].ToString() == "Admin" && dt31.Rows[0].ItemArray[2].ToString() == "Director")
                {
                    ddlApprovalDean.Visible = true;
                    ddlApprovalHead.Visible = true;
                    ddlApprovalHR.Visible = true;
                    ddlApprovalHead.Enabled = false;
                    ddlApprovalDean.Enabled = false;
                    ddlApprovalHR.Enabled = true;
                }
                //HR Salaries and Benefits
                else if (dt31.Rows[0].ItemArray[4].ToString() == "Adamson Office" && dt31.Rows[0].ItemArray[3].ToString() == "HR Salaries and Benefits" && dt31.Rows[0].ItemArray[1].ToString() == "SubAdmin" && dt31.Rows[0].ItemArray[2].ToString() == "Head")
                {
                    ddlApprovalHead.Enabled = false;
                    ddlApprovalDean.Enabled = false;
                    ddlApprovalHR.Enabled = true;
                }
                //error

                if (dt31.Rows[0].ItemArray[4].ToString() == ddlCollege.Text)
                {
                    //UserLevel
                    if (dt31.Rows[0].ItemArray[1].ToString() == "Faculty" || dt31.Rows[0].ItemArray[1].ToString() == "Employee")
                    {
                        //Position
                        if (dt31.Rows[0].ItemArray[2].ToString() == "Dean" || dt31.Rows[0].ItemArray[2].ToString() == "Director")
                        {
                            ddlApprovalHead.Enabled = true;
                            ddlApprovalDean.Enabled = false;
                            ddlApprovalHR.Enabled = false;
                        }
                    }
                }
                //APPROVAL 2
                else if (dt31.Rows[0].ItemArray[4].ToString() == ddlCollege.Text && dt31.Rows[0].ItemArray[3].ToString() == ddlDept.Text)
                {
                    //UserLevel
                    if (dt31.Rows[0].ItemArray[1].ToString() == "Faculty" || dt31.Rows[0].ItemArray[1].ToString() == "Employee")
                    {//Position
                        if (dt31.Rows[0].ItemArray[2].ToString() == "Chairperson" || dt31.Rows[0].ItemArray[2].ToString() == "Head" || dt31.Rows[0].ItemArray[2].ToString() == "Supervisor")
                        {
                            ddlApprovalHead.Enabled = false;
                            ddlApprovalDean.Enabled = true;
                            ddlApprovalHR.Enabled = false;

                        }
                    }
                }

                //Approval Enable Dept Head
                if (dt3.Rows[0].ItemArray[12].ToString() != "Denied" && dt3.Rows[0].ItemArray[12].ToString() != "Approved")
                {
                    ddlApprovalDean.Enabled = false;
                    ddlApprovalHead.Enabled = true;
                    ddlApprovalHR.Enabled = false;
                }
                else if (dt3.Rows[0].ItemArray[13].ToString() != "Denied" && dt3.Rows[0].ItemArray[13].ToString() != "Approved")
                {
                    ddlApprovalDean.Enabled = true;
                    ddlApprovalHead.Enabled = false;
                    ddlApprovalHR.Enabled = false;
                }
                else if (dt3.Rows[0].ItemArray[14].ToString() != "Denied" && dt3.Rows[0].ItemArray[14].ToString() != "Approved")
                {
                    ddlApprovalDean.Enabled = false;
                    ddlApprovalHead.Enabled = false;
                    ddlApprovalHR.Enabled = true;
                }
                else
                {
                    ddlApprovalDean.Enabled = false;
                    ddlApprovalHead.Enabled = false;
                    ddlApprovalHR.Enabled = false;
                }

            }
        }
        public void Edit()
        {
            string LeaveID = Request.QueryString["LeaveID"];
            SqlCommand cmd = new SqlCommand("UPDATE formSubstitution SET  ApprovalHead ='" + ddlApprovalHead.Text + "' WHERE(LeaveID = " + LeaveID + ")", conn);
            SqlCommand cmd22 = new SqlCommand("UPDATE formSubstitution SET  ApprovalDean ='" + ddlApprovalDean.Text + "' WHERE(LeaveID = " + LeaveID + ")", conn);
            SqlCommand cmd33 = new SqlCommand("UPDATE formSubstitution SET  ApprovalHR ='" + ddlApprovalHR.Text + "' WHERE(LeaveID = " + LeaveID + ")", conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            SqlDataAdapter da22 = new SqlDataAdapter(cmd22);
            SqlDataAdapter da33 = new SqlDataAdapter(cmd33);
            conn.Open();
            //cmd.Parameters.AddWithValue("ApprovalIDHead", ddlApprovalHead.Text);
            //cmd22.Parameters.AddWithValue("ApprovalIDDean", ddlApprovalDean.Text);
            //cmd33.Parameters.AddWithValue("ApprovalIDHR", ddlApprovalHR.Text);
            cmd.ExecuteNonQuery();
            conn.Close();

        }

        void notification()
        {
            string formCBAID = Request.QueryString["SubstitutionID"];
            string desc = "Your Application for Substitution has been \n" 
                + ddlApprovalHead.SelectedItem.Text + " by the Dept. Head/Supervisor \n"
                + ddlApprovalDean.SelectedItem.Text + " by the Dean/Director \n"
                + ddlApprovalHR.SelectedItem.Text + " by the HR Director \n";

            string sqlc = "INSERT INTO formMessegeSender " +
                "(Subject, Description, DateTime, EmployeeID, catMessegeID, fromEmpID, Readed) " +
                "VALUES ('Substitution Request','" +
                desc + "','" +
                DateTime.Now + "'," +
                cbasender(formCBAID) +
                ",2," +
                SessionID() +
                ", 0)";
            SqlCommand cmd = new SqlCommand(sqlc, conn);

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        string cbasender(string src)
        {
            string sqlx = "SELECT EmployeeID FROM formSubstitution WHERE SubstitutionID = " + src;
            SqlDataAdapter cmd = new SqlDataAdapter(sqlx, conn);
            DataTable tbl = new DataTable();

            conn.Open();
            cmd.Fill(tbl);
            conn.Close();

            return tbl.Rows[0][0].ToString();
        }


    }
}